﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Automobile.service.Model;
using System.Data;
using System.Data.SqlClient;

namespace Automobile.service.DAL
{
    public class PurchaseDal
    {
        string str = "Data Source=.;Initial Catalog=qichefuwu;Integrated Security=True";

        /// <summary>
        /// 显示采购信息
        /// </summary>
        /// <returns></returns>
        public List<PurchaseModel> Purchase()
        {
            string sql = "select Purchase_Id,Purchase_curom,Purchase_date,Purchase_State,SumMoney,Purchase_Remark,Supplier_Id,WareHose_Id,Employee_Id from Purchase";
            SqlDataAdapter adapter = new SqlDataAdapter(sql, str);
            DataTable table = new DataTable();
            adapter.Fill(table);
            List<PurchaseModel> list = new List<PurchaseModel>();
            foreach (DataRow item in table.Rows)
            {
                PurchaseModel s = new PurchaseModel();
                s.Purchase_Id = Convert.ToInt32(item["Purchase_Id"]);
                s.Purchase_curom = Convert.ToInt32(item["Purchase_curom"]);
                s.Purchase_date = Convert.ToDateTime(item["Purchase_date"]);
                s.Purchase_State = item["Purchase_State"].ToString();
                s.SumMoney = Convert.ToInt32(item["SumMoney"]);
                s.Purchase_Remark = item["Purchase_Remark"].ToString();
                s.Supplier_Id = Convert.ToInt32(item["Supplier_Id"]);
                s.WareHose_Id = Convert.ToInt32(item["WareHose_Id"]);
                s.Employee_Id = Convert.ToInt32(item["Employee_Id"]);
                list.Add(s);
            }
            return list;
        }
        /// <summary>
        /// 添加采购信息
        /// </summary>
        /// <returns></returns>
        public int AddPurchase(PurchaseModel s)
        {
            string sql = "insert into Purchase(Purchase_curom,Purchase_date,Purchase_State,SumMoney,Purchase_Remark,Supplier_Id,WareHose_Id,Employee_Id) values('" + s.Purchase_curom + "','" + s.Purchase_date + "','" + s.Purchase_State + "','" + s.SumMoney + "','" + s.Purchase_Remark + "','" + s.Supplier_Id + "','" + s.WareHose_Id + "','" + s.Employee_Id + "')";
            SqlConnection conn = new SqlConnection(str);
            conn.Open();
            SqlCommand comm = new SqlCommand(sql, conn);
            int i = comm.ExecuteNonQuery();
            conn.Close();
            return i;
        }
        /// <summary>
        /// 删除采购信息
        /// </summary>
        /// <returns></returns>
        public int DelePurchase(int Purchase_Id)
        {
            string sql = "delete from Purchase where Purchase_Id=" + Purchase_Id;
            SqlConnection conn = new SqlConnection(str);
            conn.Open();
            SqlCommand comm = new SqlCommand(sql, conn);
            int i = comm.ExecuteNonQuery();
            conn.Close();
            return i;
        }

        /// <summary>
        /// 取单个采购信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetPurchase(string Purchase_Id)
        {
            string sql = "select Purchase_Id,Purchase_curom,Purchase_date,Purchase_State,SumMoney,Purchase_Remark,Supplier_Id,WareHose_Id,Employee_Id from Purchase where Purchase_Id=" + Purchase_Id;

            SqlConnection conn = new SqlConnection(str);
            conn.Open();//打开数据库

            SqlCommand comm = new SqlCommand(sql, conn);
            DataTable table = new DataTable();
            SqlDataAdapter ada = new SqlDataAdapter(comm);
            ada.Fill(table);
            conn.Close();
            return table;
        }

        /// <summary>
        /// 更新采购信息
        /// </summary>
        /// <param name="stu"></param>
        /// <returns></returns>
        public int UpdatePurchase(PurchaseModel s)
        {
            string sql = "update Purchase set Purchase_curom='" + s.Purchase_curom + "',Purchase_date='" + s.Purchase_date + "',Purchase_State='" + s.Purchase_State + "',SumMoney='" + s.SumMoney + "',Purchase_Remark='" + s.Purchase_Remark + "',Supplier_Id='" + s.Supplier_Id + "',WareHose_Id='" + s.WareHose_Id + "',Employee_Id='" + s.Employee_Id + "' where Purchase_Id='" + s.Purchase_Id + "'";
            SqlConnection conn = new SqlConnection(str);
            conn.Open();//打开数据库

            SqlCommand comm = new SqlCommand(sql, conn);
            //执行命令
            int count = comm.ExecuteNonQuery();
            conn.Close();

            return count;
        }
    }
}
